Mysql初次Init线程不安全问题

发表于 | 更新于 | 字数统计1.1k字 | 阅读时长5分钟

现象描述

沙盒测试时发现的这个问题，在程序启动时偶发core，而且不稳定复现，查看core信息会发现core在了mysql连接上；而且最后发现的规律是每次程序启动时，如果多线程获取数据库连接，就可能出现core。core信息如图所示:

代码复现

在主线程内初始化mysql，在子线程内调用mysql_real_connect,就会导致coredump

#include <mysql.h>
#include <pthread.h>
void* func(void* arg)
{
    MYSQL* mysql = (MYSQL *)arg;
    mysql_real_connect(mysql, “127.0.0.1″, “root”, “123456″, “chen”, 1234, NULL, 0);
    mysql_close(mysql);
    return (void *)0;
}

int main()
{
    MYSQL mysql;
    if (NULL == mysql_init(&mysql))
    {
        return -1;
    }
    pthread_t thread;
    pthread_create(&thread, NULL, func, &mysql);
    pthread_join(thread, NULL);
    return 0;
}

出现原因

如官网文档所说，当我们调用mysql_real_connect()函数去获取数据库连接时，需要先调用mysql_init(MYSQL *mysql)函数去获取一个MYSQL connection handler，然而mysql_init()不是完全线程安全的，但是只要成功调用一次后就线程安全了，如果有多线程并发调用mysql_init()，第一次init时如果刚好多线程并发调用，就会出core；为啥第一次调用mysql_init时线程不安全？我们可以来看看mysql源码:

  1. mysql.h文件预定义mysql_library_init函数

    #define mysql_library_init mysql_server_init

  2. client.c文件定义mysql_init函数,并调用mysql_server_init函数

    // Init MySQL structure or allocate one

MYSQL * STDCALL
mysql_init(MYSQL *mysql)
{
  if (mysql_server_init(0, NULL, NULL))
    return 0;
  if (!mysql)
  {
    if (!(mysql=(MYSQL*) my_malloc(sizeof(*mysql),MYF(MY_WME | MY_ZEROFILL))))
    {
      set_mysql_error(NULL, CR_OUT_OF_MEMORY, unknown_sqlstate);
      return 0;
    }
    mysql->free_me=1;
  }
  else
    memset(mysql, 0, sizeof(*(mysql)));
  mysql->charset=default_client_charset_info;
  strmov(mysql->net.sqlstate, not_error_sqlstate);

  /*
    Only enable LOAD DATA INFILE by default if configured with option
    ENABLED_LOCAL_INFILE
  */

#if defined(ENABLED_LOCAL_INFILE) && !defined(MYSQL_SERVER)
  mysql->options.client_flag|= CLIENT_LOCAL_FILES;
#endif

#ifdef HAVE_SMEM
  mysql->options.shared_memory_base_name= (char*) def_shared_memory_base_name;
#endif

  mysql->options.methods_to_use= MYSQL_OPT_GUESS_CONNECTION;
  mysql->options.report_data_truncation= TRUE;  /* default */

  mysql->reconnect= 0;

  mysql->options.secure_auth= TRUE;

  return mysql;
}

  3. libmysql.c文件定义mysql_server_init函数, 问题就出在这个函数,用了mysql_client_init这个标记量来判断是否需要调用my_thread_init函数，如果mysql_client_init==1就直接为每个线程初始化私有变量，否则会先去初始化一些全局性的系统函数，资源和变量; 所以如果第一次init时出现多线程并发情景，线程A将mysql_client_init变量置为1，紧接着初始化全局资源，与此同时线程B走了else分支，直接开始调用my_thread_init函数，此时就会报错了，core由此产生。

    int STDCALL mysql_server_init(int argc __attribute__((unused)),
                  char **argv __attribute__((unused)),
                  char **groups __attribute__((unused)))
{
  int result= 0;
  if (!mysql_client_init)
  {
    mysql_client_init=1;
    org_my_init_done=my_init_done;
    if (my_init())                /* Will init threads */
      return 1;
    init_client_errs();
    if (mysql_client_plugin_init())
      return 1;
    if (!mysql_port)
    {
      char *env;
      struct servent *serv_ptr __attribute__((unused));

      mysql_port = MYSQL_PORT;

      /*
        if builder specifically requested a default port, use that
        (even if it coincides with our factory default).
        only if they didn't do we check /etc/services (and, failing
        on that, fall back to the factory default of 3306).
        either default can be overridden by the environment variable
        MYSQL_TCP_PORT, which in turn can be overridden with command
        line options.
      */

#if MYSQL_PORT_DEFAULT == 0
      if ((serv_ptr= getservbyname("mysql", "tcp")))
        mysql_port= (uint) ntohs((ushort) serv_ptr->s_port);
#endif
      if ((env= getenv("MYSQL_TCP_PORT")))
        mysql_port=(uint) atoi(env);
    }

    if (!mysql_unix_port)
    {
      char *env;
#ifdef __WIN__
      mysql_unix_port = (char*) MYSQL_NAMEDPIPE;
#else
      mysql_unix_port = (char*) MYSQL_UNIX_ADDR;
#endif
      if ((env = getenv("MYSQL_UNIX_PORT")))
    mysql_unix_port = env;
    }
    mysql_debug(NullS);
#if defined(SIGPIPE) && !defined(__WIN__)
    (void) signal(SIGPIPE, SIG_IGN);
#endif
#ifdef EMBEDDED_LIBRARY
    if (argc > -1)
       result= init_embedded_server(argc, argv, groups);
#endif
  }
  else
    result= (int)my_thread_init();         /* Init if new thread */
  return result;
}

  4. 官方文档

    • mysql_real_connect() attempts to establish a connection to a MySQL database engine running on host. mysql_real_connect() must complete successfully before you can execute any other API functions that require a valid MYSQL connection handler structure.

    • MYSQL *mysql_init(MYSQL *mysql)

      Allocates or initializes a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, the function allocates, initializes, and returns a new object. Otherwise, the object is initialized and the address of the object is returned. If mysql_init() allocates a new object, it is freed when mysql_close() is called to close the connection.

      In a nonmulti-threaded environment, mysql_init() invokes mysql_library_init() automatically as necessary. However, mysql_library_init() is not thread-safe in a multi-threaded environment, and thus neither is mysql_init(). Before calling mysql_init(), either call mysql_library_init() prior to spawning any threads, or use a mutex to protect the mysql_library_init() call. This should be done prior to any other client library call.

解决方案

  1. 每一次连接数据库时都先后加锁调用mysql_init()和mysql_real_connect()，确保init先于connect函数被调用过，而且不会被其他线程并发调用，以免初次连接数据库时多线程并发导致core
  2. 在程序启动时先全局调用一次mysql_init()函数，确保初次调用mysql_init时是线程安全的，之后的调用mysql_real_connect函数就不会出core
  3. MySQL提供了线程安全的库libmysql_r，可以考虑替换原来的线程不安全的libmysql库